package database;

import gui.ExceptionEvent;

import java.sql.*;
import java.util.Calendar;

public class CustData extends DataSuperclass {
	public CustData() {
		con = OracleConnection.getInstance().getConnection();
	}

	public boolean registerCustomer(int cid, String password, String name, String address, String phone){
		try{
			ps = con.prepareStatement("INSERT INTO customer VALUES (?,?,?,?,?)");
			ps.setInt(1, cid);
			ps.setString(2, password);
			ps.setString(3, name);
			ps.setString(4, address);
			ps.setString(5, phone);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return true;
			} else {
				return false;
			}
		
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return false;
		}
	}
	
	public boolean isUser(int cid){
		try{
			ps = con.prepareStatement("SELECT cust_id FROM customer WHERE cust_id = ?");
			ps.setInt(1, cid);
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				return true;
			}else{
				return false;
			}

		
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return false;
		}
	}
	
	public int findStock(int upc){
		try{
			ps = con.prepareStatement("SELECT stock FROM item WHERE upc = ?");
			ps.setInt(1, upc);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			} else {
				return -1;
			}
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			return -2;
		}
	}
	
	public int loggedIn(int rec){
		try{
			ps = con.prepareStatement("SELECT cust_id FROM purchase WHERE receipt_id = ?");
			ps.setInt(1, rec);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			} else {
				return 0;
			}
		
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return 0;
		}
	}
	
	public int quantityPurchasing(int rec, int upc){
		try{
			ps = con.prepareStatement("SELECT purchase_qty FROM purchase_item WHERE receipt_id = ? AND upc = ?");
			ps.setInt(1, rec);
			ps.setInt(2, upc);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			} else {
				return -1;
			}
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			return -2;
		}
	}
	
	public boolean userLogin(int cid, String password){
		try{
			ps = con.prepareStatement("SELECT cust_id FROM customer WHERE cust_id = ? AND password = ?");
			ps.setInt(1, cid);
			ps.setString(2, password);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return true;
			} else {
				return false;
			}
		
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return false;
		}
	}
	
	public boolean findReceiptAndUPC(int rec, int upc) {

		try {
			ps = con.prepareStatement("SELECT upc FROM purchase_item where upc = ? AND receipt_id = ?");
			ps.setInt(1, upc);
			ps.setInt(2, rec);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return true;
			} else {
				return false;
			}
		
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return false;
		}

	}

	public int getMaxReceipt() {
		try {
			ps = con.prepareStatement("SELECT max(receipt_id) FROM purchase");

			ResultSet rs = ps.executeQuery();
			rs.next();

			return rs.getInt(1);
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			return -1;
		}

	}

	public ResultSet editCustomers() {
		try {
			ps = con.prepareStatement(
					"SELECT * from customer",
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE); // you can't edit once you join
													// another table!?!
			

			ResultSet rs = ps.executeQuery();
			return rs;
		} catch (SQLException ex) {

			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return null;
		}
	}

	public boolean adjustTuple(int rec, int upc, int qty) {
		try {

			ps = con.prepareStatement("UPDATE purchase_item SET purchase_qty = ? WHERE receipt_id = ? AND upc = ?");

			ps.setInt(1, qty);
			ps.setInt(2, rec);
			ps.setInt(3, upc);
			ps.executeQuery();

			return true;

		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			return false;
		}
	}

	public ResultSet showItemsWithCategory(String category, int quantity){
		try {
			ps = con.prepareStatement("SELECT s.upc, i.category, i.title, s.singer_name, i.price, i.stock FROM lead_singer s join item i on s.upc=i.upc WHERE category = ?",
			        ResultSet.TYPE_SCROLL_INSENSITIVE,
			        ResultSet.CONCUR_UPDATABLE);
			ps.setString(1, category);
			ResultSet rs = ps.executeQuery();
			return rs; 
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return null;
		}
	}
	public ResultSet showItemsWithSinger(String singer, int quantity){
		try {
			ps = con.prepareStatement("SELECT s.upc, i.category, i.title, s.singer_name, i.price, i.stock FROM lead_singer s join item i on s.upc=i.upc WHERE singer_name = ?",
			        ResultSet.TYPE_SCROLL_INSENSITIVE,
			        ResultSet.CONCUR_UPDATABLE);
			ps.setString(1, singer);
			ResultSet rs = ps.executeQuery();
			return rs;
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return null;
		}
	}
	
	
	public ResultSet showItemsWithTitle(String title, int quantity){
		try {
			ps = con.prepareStatement("SELECT s.upc, i.category, i.title, s.singer_name, i.price, i.stock FROM lead_singer s join item i on s.upc=i.upc WHERE title = ?",
			        ResultSet.TYPE_SCROLL_INSENSITIVE,
			        ResultSet.CONCUR_UPDATABLE);
			ps.setString(1, title);
			ResultSet rs = ps.executeQuery();
			return rs;
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return null;
		}
	}
	public ResultSet showItemsWithCatAndTit(String category, String title, int quantity){
		try {
			ps = con.prepareStatement("SELECT s.upc, i.category, i.title, s.singer_name, i.price, i.stock FROM lead_singer s join item i on s.upc=i.upc WHERE category = ? AND title = ?",
			        ResultSet.TYPE_SCROLL_INSENSITIVE,
			        ResultSet.CONCUR_UPDATABLE);
			ps.setString(1, category);
			ps.setString(2, title);
			ResultSet rs = ps.executeQuery();
			return rs;
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return null;
		}
	}
	public ResultSet showItemsWithCatAndSing(String category, String singer, int quantity){
		try {
			ps = con.prepareStatement("SELECT s.upc, i.category, i.title, s.singer_name, i.price, i.stock FROM lead_singer s join item i on s.upc=i.upc WHERE category = ? And singer_name = ?",
			        ResultSet.TYPE_SCROLL_INSENSITIVE,
			        ResultSet.CONCUR_UPDATABLE);
			ps.setString(1, category);
			ps.setString(2, singer);
			ResultSet rs = ps.executeQuery();
			return rs;
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return null;
		}
	}
	public ResultSet showItemsWithCatTitSing(String category, String title, String singer, int quantity){
		try {
			ps = con.prepareStatement("SELECT s.upc, i.category, i.title, s.singer_name, i.price, i.stock FROM lead_singer s join item i on s.upc=i.upc WHERE category = ? AND title = ? AND singer_name = ?",
			        ResultSet.TYPE_SCROLL_INSENSITIVE,
			        ResultSet.CONCUR_UPDATABLE);
			ps.setString(1, category);
			ps.setString(2, title);
			ps.setString(3, singer);
			ResultSet rs = ps.executeQuery();
			return rs;
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return null;
		}
	}
	public ResultSet showItemsWithTitAndSing(String title, String singer, int quantity){
		try {
			ps = con.prepareStatement("SELECT s.upc, i.category, i.title, s.singer_name, i.price, i.stock FROM lead_singer s join item i on s.upc=i.upc WHERE title = ? AND singer_name = ?",
			        ResultSet.TYPE_SCROLL_INSENSITIVE,
			        ResultSet.CONCUR_UPDATABLE);
			ps.setString(1, title);
			ps.setString(2, singer);
			ResultSet rs = ps.executeQuery();
			return rs;
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return null;
		}
	}
	public boolean isDelDateNull(int rec) {
		try {
			// get receipt id if ship date is null
			ps = con.prepareStatement("SELECT * from purchase where receipt_id = ? AND delivered_date IS NULL");
			ps.setInt(1, rec);
			ResultSet rs = ps.executeQuery();
			// if there is a result then true
			return rs.next();

		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return false;
		}
	}

	public boolean setDelDate(int rec) {
		try {
			ps = con.prepareStatement("UPDATE purchase SET delivered_date = ? WHERE receipt_id = ?");
			ps.setString(1, date());
			ps.setInt(2, rec);
			ps.executeQuery();
			return true;

		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return false;
		}
	}

	public boolean addPurchaseItem(Integer rec, Integer upc, Integer qty) {

		try {
			// constraint: needs existing rec entry in purchase table
			// handled in application logic
			
			ps = con.prepareStatement("INSERT INTO purchase_item VALUES (?,?,?)");
			ps.setInt(1, rec);
			ps.setInt(2, upc);
			ps.setInt(3, qty);
			ps.executeUpdate();
			con.commit();
			return true;

		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			try {
				con.rollback();
				return false;

			} catch (SQLException ex2) {
				event = new ExceptionEvent(this, ex2.getMessage());
				fireExceptionGenerated(event);
				return false;
			}
		}
	}

	private String date() {
		String year = String.valueOf(Calendar.getInstance().get(Calendar.YEAR));
		String month = String.valueOf(Calendar.getInstance().get(Calendar.MONTH));
		String day = String.valueOf(Calendar.getInstance().get(Calendar.DAY_OF_MONTH));
		return year + "-" + month + "-" + day;
	}
	
	public boolean addCard(int rec, float cardno, int exp){
		try{
			ps = con.prepareStatement("UPDATE purchase SET card_num = ? AND expiry_date = ? WHERE rec = ?");
			ps.setFloat(1, cardno);
			ps.setInt(2, exp);
			ps.setInt(3, rec);
			ps.executeUpdate();
			
			return true;
		}  catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return false;
		}
	}
	
	public boolean setExpDelDate(int rec, Date date){
		try{
			ps = con.prepareStatement("UPDATE purchase SET expected_date = ? WHERE receipt_id = ?");
			ps.setDate(1, date);
			ps.setInt(2, rec);
			ps.executeUpdate();
			return true;
			
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return false;
		}
	}

	public boolean addPurchase(int rec, int cid) {

		try {
			ps = con.prepareStatement("INSERT INTO purchase VALUES (?,null,?,null,null,null,null)");
			ps.setInt(1, rec);
			ps.setInt(2, cid);
			ps.executeUpdate();
			con.commit();
			return true;

		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			try {
				con.rollback();
				return false;
		
			} catch (SQLException ex2) {
				event = new ExceptionEvent(this, ex2.getMessage());
				fireExceptionGenerated(event);
				return false;
			}
		}
	}
}
